﻿Imports MySql.Data.MySqlClient
Public Class Form4
    Dim con As MySqlConnection
    Dim cmd As MySqlCommand = Nothing
    Dim dt As New DataTable
    Dim bsource As BindingSource
    Dim reader As MySqlDataReader
    Dim da As MySqlDataAdapter
    Dim result As Integer
    Dim query As String
   

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtauthor.TextChanged

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim con As New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=;database=library_database "
        Try
            con.Open()

            query = "update library_database.book_details set book_id_no='" & txtidno.Text & "',name_of_book='" & txtnob.Text & "',author='" & txtauthor.Text & "',quantity='" & txtquantity.Text & "' where book_id_no='" & txtidno.Text & "' "
            Dim cmd As New MySqlCommand(query, con)
            cmd.ExecuteNonQuery()
            MessageBox.Show("data successfully update")

            txtidno.Text = ""
            txtnob.Text = ""
            txtauthor.Text = ""
            txtquantity.Text = ""
            txtidno.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Form5.Show()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Form2.Show()
        Me.Hide()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim con As New MySqlConnection("server=localhost;userid=root;password=;database=library_database ")
        Try

            con.Open()
            cmd = New MySqlCommand("select * from library_database.book_details where book_id_no='" & txtidno.Text & "' or name_of_book='" & txtnob.Text & "' ", con)
            result = cmd.ExecuteScalar
            con.Close()
            If result = 0 Then
                con.Open()
                cmd = New MySqlCommand("insert into library_database.book_details (book_id_no,name_of_book,author,quantity)values('" & txtidno.Text & "','" & txtnob.Text & "','" & txtauthor.Text & "','" & txtquantity.Text & "')", con)
                cmd.ExecuteNonQuery()
                MessageBox.Show("data are successfully save")
                con.Close()
            Else
                MessageBox.Show("data is already exist")
            End If
            txtidno.Text = ""
            txtnob.Text = ""
            txtauthor.Text = ""
            txtquantity.Text = ""
            txtidno.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim con As New MySqlConnection("server=localhost;userid=root;password=;database=library_database ")

        Try
            con.Open()
            cmd = New MySqlCommand("select * from library_database.book_details where book_id_no='" & txtidno.Text & "' and name_of_book='" & txtnob.Text & "'  ", con)
            result = cmd.ExecuteScalar
            con.Close()
            If result >= 1 Then
                con.Open()
                cmd = New MySqlCommand("delete from book_details where book_id_no='" & txtidno.Text & "' and name_of_book='" & txtnob.Text & "' ", con)
                cmd.ExecuteNonQuery()
                MessageBox.Show("data are successfully deleted")
                con.Close()
            Else
                MessageBox.Show("no data found ")
            End If
            txtidno.Text = ""
            txtnob.Text = ""
            txtauthor.Text = ""
            txtquantity.Text = ""
            txtidno.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub



    Private Sub txtnob_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtnob.TextChanged

    End Sub

    Private Sub TextBox1_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch2.TextChanged
        Dim dv As New DataView(dt)
        dv.RowFilter = String.Format("name_of_book like '%{0}%' ", txtsearch2.Text)
        DataGridView1.DataSource = dv
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim con As New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=;database=library_database "
        Dim da As New MySqlDataAdapter
        Dim bsource As New BindingSource
        Try
            con.Open()
            Dim query As String
            query = "select * from library_database.book_details "
            Dim cmd As New MySqlCommand(query, con)
            da.SelectCommand = cmd
            da.Fill(dt)
            bsource.DataSource = dt
            DataGridView1.DataSource = bsource
            da.Update(dt)
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try

    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        dt.Clear()
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Me.Close()
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub
End Class